Amazon Redshift: 現在のテーブル定義からCREATE TABLE文を生成するSQLを頑張って書いてみた(けれど…)

先日投稿した以下エントリでは、既存構築済のテーブル内容から諸々構成情報を引っこ抜いてCREATE TABLE文を作り直す、という方法について一つの方法を書き記してみていました。ですが2つ(CREATE TABLE文、コメント付与文)書いた方のうちの前者はスクリプト言語(Groovy)で書いていたので若干妥協してしまった感もあり、何とかしてSQLで書けないかな〜と思っておりました。

• Amazon Redshift: 現在のテーブル定義からCREATE TABLE文などをリバースエンジニアリングする ｜ Developers.IO

当エントリはその続編的エントリとなります。

目次

• 検証用CREATE TABLE文
• CREATE TABLE文を生成する(by SQL)
• 実行結果
• まとめ

検証用CREATE TABLE文

まず用意したのは以下CREATE TABLE文3つ。

分散スタイル: EVEN

こちらは分散スタイル: EVENのテーブル。テーブルを構成している項目のデータ型と列圧縮タイプはひと通り網羅する感じで、あと分散キー指定は無し＝EVENで構成しています。

CREATE TABLE public.testtable_a_dist_even (
  id SMALLINT encode mostly8 NOT NULL, /** mostly8 */
  num_int INT encode mostly16 NOT NULL, /** mostly16 */
  num_biging BIGINT encode mostly32 NOT NULL, /** mostly32 */
  num_decimal DECIMAL(5,2) encode mostly16 NOT NULL,
  num_real REAL encode runlength NOT NULL, /** runlength */
  num_double DOUBLE PRECISION,   /** raw(無圧縮) */
  value_boolean BOOLEAN,
  value_char CHAR(5) encode bytedict NOT NULL, /** bytedict */
  value_varchar VARCHAR(100) encode lzo NOT NULL, /** lzo */
  value_date DATE encode delta NOT NULL, /** delta */
  value_timestamp TIMESTAMP encode delta32k NOT NULL, /** delta32k */
  value_varchar_text255 VARCHAR(100) encode text255 NOT NULL, /** text255 */
  value_varchar_text32k VARCHAR(100) encode text32k NOT NULL  /** text32k */
)
sortkey(id, value_char, num_int);

分散スタイル: KEY

こちらは分散キーを指定した場合のテーブル。上記テーブル構成と異なるのは分散キー指定の部分が追加されたところだけです。

CREATE TABLE public.testtable_a_dist_key (
  id SMALLINT encode mostly8 NOT NULL, /** mostly8 */
  num_int INT encode mostly16 NOT NULL, /** mostly16 */
  num_biging BIGINT encode mostly32 NOT NULL, /** mostly32 */
  num_decimal DECIMAL(5,2) encode mostly16 NOT NULL,
  num_real REAL encode runlength NOT NULL, /** runlength */
  num_double DOUBLE PRECISION,   /** raw(無圧縮) */
  value_boolean BOOLEAN,
  value_char CHAR(5) encode bytedict NOT NULL, /** bytedict */
  value_varchar VARCHAR(100) encode lzo NOT NULL, /** lzo */
  value_date DATE encode delta NOT NULL, /** delta */
  value_timestamp TIMESTAMP encode delta32k NOT NULL, /** delta32k */
  value_varchar_text255 VARCHAR(100) encode text255 NOT NULL, /** text255 */
  value_varchar_text32k VARCHAR(100) encode text32k NOT NULL  /** text32k */
)
  distkey(id)
  sortkey(id, value_char, num_int);

分散スタイル: ALL

そして3つ目が分散スタイル: ALL。こちらも分散キー指定同様、異なるのは分散スタイルを『diststyle ALL』にしている箇所のみです。

CREATE TABLE public.testtable_a_dist_all (
  id SMALLINT encode mostly8 NOT NULL, /** mostly8 */
  num_int INT encode mostly16 NOT NULL, /** mostly16 */
  num_biging BIGINT encode mostly32 NOT NULL, /** mostly32 */
  num_decimal DECIMAL(5,2) encode mostly16 NOT NULL,
  num_real REAL encode runlength NOT NULL, /** runlength */
  num_double DOUBLE PRECISION,   /** raw(無圧縮) */
  value_boolean BOOLEAN,
  value_char CHAR(5) encode bytedict NOT NULL, /** bytedict */
  value_varchar VARCHAR(100) encode lzo NOT NULL, /** lzo */
  value_date DATE encode delta NOT NULL, /** delta */
  value_timestamp TIMESTAMP encode delta32k NOT NULL, /** delta32k */
  value_varchar_text255 VARCHAR(100) encode text255 NOT NULL, /** text255 */
  value_varchar_text32k VARCHAR(100) encode text32k NOT NULL  /** text32k */
)
  diststyle ALL
  sortkey(id, value_char, num_int);

CREATE TABLE文を生成する(by SQL)

そして以下が対象テーブルのCREATE TABLE文を引っこ抜くSQL文です。だいぶ長くなってしまいましたが、SQL文中に含まれている<スキーマ名>及び<テーブル名>に対象テーブルの情報を記載・実行してもらえればCREATE TABLE文が生成されます。ざっくり解説しますと、ソート番号的な項目(attnum)と生成するSQL文テキスト(sqlstmt)をひたすら連結し、ソート番号で並べつつ最後にSQL文だけ取得している感じです。途中出てくる2000とかの数字は適当です。項目の並びが意図した通りになるのであればOKです。

SELECT
  sqlstmt
FROM (

  /** CREATE TABLE文: 作成テーブル名の宣言 */
  (SELECT 0 AS attnum, 'CREATE TABLE ' || '<スキーマ名>.<テーブル名> (' AS sqlstmt) 
  UNION ALL

  /** CREATE TABLE文: テーブル内各項目定義 */
  (
  SELECT
    def_comment.attnum AS attnum,
    '   '
    || pg_table_def.column
    || ' '
    || UPPER(pg_table_def.type)
    || ' encode ' || CASE pg_table_def.encoding WHEN 'none' THEN 'raw' ELSE pg_table_def.encoding END
    || CASE pg_table_def.notnull WHEN TRUE THEN ' NOT NULL' ELSE ' ' END
    || ',' AS sqlstmt
  FROM
    pg_table_def,
    (
    SELECT
      pg_stat_user_tables.schemaname AS schema,
      pg_stat_user_tables.relname    AS tablename,
      tablecom.description           AS table_comment,
      pg_attribute.attname           AS columnname,
      colcom.description             AS column_comment,
      pg_attribute.attnum,
      pg_attrdef.adsrc               AS default
    FROM
      pg_stat_user_tables, pg_type, pg_attribute
      LEFT JOIN pg_description tablecom ON pg_attribute.attrelid = tablecom.objoid AND tablecom.objsubid = 0
      LEFT JOIN pg_description colcom ON pg_attribute.attnum = colcom.objsubid AND pg_attribute.attrelid = colcom.objoid
      LEFT JOIN pg_attrdef ON pg_attribute.attrelid = pg_attrdef.adrelid AND pg_attribute.attnum = pg_attrdef.adnum
    WHERE
      pg_attribute.attrelid = pg_stat_user_tables.relid
      AND pg_attribute.atttypid = pg_type.oid
      AND pg_attribute.attnum > 0
      AND pg_stat_user_tables.relname IN (
        SELECT pg_class.relname
        FROM
          pg_namespace
          JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
        WHERE
          pg_class.relkind = 'r'
          AND pg_class.relname NOT LIKE 'pg_%'
          AND pg_class.relname NOT LIKE 'sql_%')
      AND pg_stat_user_tables.schemaname = '<スキーマ名>'
      AND pg_stat_user_tables.relname = '<テーブル名>'
    ORDER BY
      pg_stat_user_tables.schemaname,
      pg_stat_user_tables.relname,
      pg_attribute.attnum
   ) def_comment
  WHERE 
    pg_table_def.tablename = def_comment.tablename
    AND pg_table_def.column = def_comment.columnname
    AND pg_table_def.schemaname = '<スキーマ名>'
    AND pg_table_def.tablename = '<テーブル名>'
  ORDER BY
    def_comment.schema,
    def_comment.tablename,
    def_comment.attnum
  )

  /** CREATE TABLE文: 各種項目定義終わり */
  UNION ALL
  (SELECT 1000 AS attnum, ')' AS sqlstmt)
    
  /** CREATE TABLE文: 分散スタイル指定 */
  UNION ALL
  (
  SELECT DISTINCT
    2000 AS attnum,
    CASE pg_class.reldiststyle
    /** 1:KEY(分散キー指定) */WHEN 1 THEN 'distkey(' || pg_table_def.column || ')'
    /** 0:EVEN */WHEN 0 THEN 'diststyle EVEN' /** OR '' */
    END AS sqlstmt
  FROM
    pg_table_def,
    pg_class
  WHERE
    pg_table_def.schemaname = '<スキーマ名>'
    AND pg_table_def.tablename = '<テーブル名>'
    AND pg_table_def.distkey = TRUE
    AND pg_table_def.tablename = pg_class.relname
  )
  UNION ALL
  (
  SELECT DISTINCT attnum, sqlstmt
    FROM
    (SELECT
      2000 AS attnum,
      CASE pg_class.reldiststyle
        /** 8:ALL */WHEN 8 THEN 'diststyle ALL'
      END AS sqlstmt
    FROM
      pg_table_def,
      pg_class
    WHERE
      pg_table_def.schemaname = '<スキーマ名>'
      AND pg_table_def.tablename = '<テーブル名>'
      AND pg_table_def.distkey = FALSE
      AND pg_table_def.tablename = pg_class.relname)
  )    
  /** CREATE TABLE文: ソートキー指定 */
  UNION ALL
  (
  SELECT sortkey AS attnum, sortitemtable.sortitem AS sqlstmt FROM
    (SELECT sortkey, sortitem FROM
      (SELECT 20000 AS sortkey, 'sortkey(' AS sortitem)
      UNION ALL
      (
      SELECT
        20100 + pg_table_def.sortkey,
        pg_table_def.column || ',' AS sortitem
      FROM
        pg_table_def
      WHERE
        pg_table_def.schemaname = '<スキーマ名>'
        AND pg_table_def.tablename = '<テーブル名>'
        AND pg_table_def.sortkey != 0
      ORDER BY 
        pg_table_def.sortkey
      )
      UNION ALL
      (SELECT 21000 AS sortkey, ')'AS sortitem)
    ) sortitemtable)
  )
ORDER BY attnum;

実行結果

上記SQLを実行してみた結果が以下となります。

分散スタイル: EVEN

# SELECT
#   sqlstmt
# FROM (
  :
  (中略)
  :
# ORDER BY attnum;
                                 sqlstmt                                  
--------------------------------------------------------------------------
 CREATE TABLE public.testtable_a_dist_even (
    id SMALLINT encode mostly8 NOT NULL,
    num_int INTEGER encode mostly16 NOT NULL,
    num_biging BIGINT encode mostly32 NOT NULL,
    num_decimal NUMERIC(5,2) encode mostly16 NOT NULL,
    num_real REAL encode runlength NOT NULL,
    num_double DOUBLE PRECISION encode raw ,
    value_boolean BOOLEAN encode raw ,
    value_char CHARACTER(5) encode bytedict NOT NULL,
    value_varchar CHARACTER VARYING(100) encode lzo NOT NULL,
    value_date DATE encode delta NOT NULL,
    value_timestamp TIMESTAMP WITHOUT TIME ZONE encode delta32k NOT NULL,
    value_varchar_text255 CHARACTER VARYING(100) encode text255 NOT NULL,
    value_varchar_text32k CHARACTER VARYING(100) encode text32k NOT NULL,
 )
 
 sortkey(
 id,
 value_char,
 num_int,
 )
(21 rows)

分散スタイル: KEY

# SELECT
#   sqlstmt
# FROM (
  :
  (中略)
  :
# ORDER BY attnum;
                                 sqlstmt                                  
--------------------------------------------------------------------------
 CREATE TABLE public.testtable_a_dist_key (
    id SMALLINT encode mostly8 NOT NULL,
    num_int INTEGER encode mostly16 NOT NULL,
    num_biging BIGINT encode mostly32 NOT NULL,
    num_decimal NUMERIC(5,2) encode mostly16 NOT NULL,
    num_real REAL encode runlength NOT NULL,
    num_double DOUBLE PRECISION encode raw ,
    value_boolean BOOLEAN encode raw ,
    value_char CHARACTER(5) encode bytedict NOT NULL,
    value_varchar CHARACTER VARYING(100) encode lzo NOT NULL,
    value_date DATE encode delta NOT NULL,
    value_timestamp TIMESTAMP WITHOUT TIME ZONE encode delta32k NOT NULL,
    value_varchar_text255 CHARACTER VARYING(100) encode text255 NOT NULL,
    value_varchar_text32k CHARACTER VARYING(100) encode text32k NOT NULL,
 )
 distkey(id)
 
 sortkey(
 id,
 value_char,
 num_int,
 )
(22 rows)

分散スタイル: ALL

# SELECT
#   sqlstmt
# FROM (
  :
  (中略)
  :
# ORDER BY attnum;
                                 sqlstmt                                  
--------------------------------------------------------------------------
 CREATE TABLE public.testtable_a_dist_all (
    id SMALLINT encode mostly8 NOT NULL,
    num_int INTEGER encode mostly16 NOT NULL,
    num_biging BIGINT encode mostly32 NOT NULL,
    num_decimal NUMERIC(5,2) encode mostly16 NOT NULL,
    num_real REAL encode runlength NOT NULL,
    num_double DOUBLE PRECISION encode raw ,
    value_boolean BOOLEAN encode raw ,
    value_char CHARACTER(5) encode bytedict NOT NULL,
    value_varchar CHARACTER VARYING(100) encode lzo NOT NULL,
    value_date DATE encode delta NOT NULL,
    value_timestamp TIMESTAMP WITHOUT TIME ZONE encode delta32k NOT NULL,
    value_varchar_text255 CHARACTER VARYING(100) encode text255 NOT NULL,
    value_varchar_text32k CHARACTER VARYING(100) encode text32k NOT NULL,
 )
 diststyle ALL
 sortkey(
 id,
 value_char,
 num_int,
 )
(21 rows)

まとめ

以上、Amazon Redshiftに於けるCREATE TABLE文をSQLで頑張って生成してみたエントリでした。

 

でしたが！実はこのSQL、完全ではありません。御覧頂くと分かるように、『各列項目の最後となる部分』及び『ソートキーの最後の部分』のカンマが付いたままですので、このままでは実行してもエラーとなります。私のSQL脳とRedshift脳ではこの部分を解決出来るに至らず『もう、実行する前にカンマ削ればええやん(by 悪魔)』が『何としてもSQLでやり切るッ！(by 天使)』を上回ってしまいました。ですのでご利用の際にはその辺りを御留意ください。m(_ _)m (ブログタイトルが若干歯切れの悪い文末としているのはこれが理由です)

あと、思い当たる節としては以下内容辺りでしょうか。

• CREATE TABLE文で宣言した項目型とCREATE TABLE文作成SQLで出来たSQL文の項目型の表記が違っている(CHAR→CHARACTER, VARCHAR→CHARACTER VARYING、DECIMAL→NUMERIC等)。まぁこれはRedshift側で吸収してくれるので気にしないor最初からこちらの定義で宣言すべき、という事象ではありますが...
  • データ型 - Amazon Redshift
• CREATE TABLE文でソートキー指定が無かった場合のケースが漏れている
• あと、可能ならばソートキーは横に並べたかった
• プライマリーキー指定が未対応(これはそもそも情報が取れるのか、という問題もありそうですが)

という訳で、甚だ不完全な情報ではございますが、不完全部分については今後も継続してチャレンジして行く所存でございますので当エントリはこの完成度での公開を御了承頂ければと思います。m(_ )m m( _)m

こちらからは以上です。

その他参考情報：

• Amazon Redshift: 現在のテーブル定義からCREATE TABLE文などをリバースエンジニアリングする ｜ Developers.IO
• Amazon Redshiftで新規追加したスキーマを検索パスに含める ｜ Developers.IO：対象スキーマを新規作成した際には登録が必要です。